package com.servlets;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SearchDAO {

	public void validateUser(int custNum) throws SQLException {

		Connection connection = null;
		java.sql.CallableStatement statement = null;
	 
		ResultSet resultSet = null;
		List<String> list = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");

			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/classicmodels", "root", "root");

			statement = connection.prepareCall("call validateUserIfo(?,?,?)");
			statement.setInt(1, custNum);
			statement.registerOutParameter(2, java.sql.Types.VARCHAR);
			statement.registerOutParameter(3, java.sql.Types.VARCHAR);
			
			statement.executeUpdate();
			

			String password =  statement.getString(2);
			String custName =  statement.getString(3);
			 

			System.out.println(" Customer Name  -"+custName +"   Pasword is -- "+password);
			 

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally {

			 
			if (statement != null) {
				statement.close();
			}

			if (connection != null) {

				connection.close();
			}
		}
	 

	}
	 
	
	
	public static void main(String[] args) throws SQLException {
		new SearchDAO().validateUser(103);
	}
	
	
	

	public List<String> SearchByCountryCode(String countryCode)
			throws SQLException {

		Connection connection = null;
		java.sql.PreparedStatement statement = null;
		// Question
		ResultSet resultSet = null;
		List<String> list = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");

			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/world", "root", "root");

			String sql = "select * from city where Countrycode=? and name=?";

			statement = connection.prepareStatement(sql);

			statement.setString(1, countryCode);
			statement.setString(2, "URINPUT");

			resultSet = statement.executeQuery();

			list = new ArrayList<String>();

			while (resultSet.next()) {

				list.add(resultSet.getString("name"));

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally {

			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connection != null) {

				connection.close();
			}
		}
		return list;
	}

}
